﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Simple.Mapper.Configuration;

namespace MySql.Simple.Mapper.Structures
{
    /// <summary>
    /// Wrapper class for the database connection
    /// </summary>
    public class Connection
    {
        const string ErrorMessage = @"An error occured executing the SQL:

{0}

The error returned was:

{1}";
        private const string MySqlConnectionString = @"Server={0}; Uid={1};pwd={2}; Database={3}; Port={4}; pooling=true;";

        private readonly string connectionString;

        private Connection(string server, string database, string username, string password, int port)
        {
            // Assign properties
            Server = server;
            Database = database;
            Username = username;
            Password = password;
            Port = port;

            // Generate connection string
            connectionString = string.Format(MySqlConnectionString, server, username, password, database, port);
        }

        #region User Specified Connection
        
        /// <summary>
        /// Creates a connection to the specified server.
        /// </summary>
        /// <param name="server">The server.</param>
        /// <param name="database">The database.</param>
        /// <returns></returns>
        public static Connection Create(string server, string database)
        {
            return Create(server, database, "root", string.Empty);
        }

        /// <summary>
        /// Creates a connection to the specified server.
        /// </summary>
        /// <param name="server">The server.</param>
        /// <param name="database">The database.</param>
        /// <param name="username">The username.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public static Connection Create(string server, string database, string username, string password)
        {
            return Create(server, database, username, password, 3306);
        }

        /// <summary>
        /// Creates a connection to the specified server.
        /// </summary>
        /// <param name="server">The server.</param>
        /// <param name="database">The database.</param>
        /// <param name="username">The username.</param>
        /// <param name="password">The password.</param>
        /// <param name="port">The port.</param>
        /// <returns></returns>
        public static Connection Create(string server, string database, string username, string password, int port)
        {
            return new Connection(server, database, username, password, port);
        }

        #endregion

        /// <summary>
        /// Creates the connection from the first connection string.
        /// </summary>
        /// <returns></returns>
        public static Connection Create()
        {
            var section = (ConnectionElementSection)ConfigurationManager.GetSection("mySqlMapper");

            foreach(ConnectionElement connection in section.Connections)
            {
                return Create(connection.Server, 
                              connection.Database, 
                              connection.Username, 
                              connection.Password,
                              connection.Port);
            }

            throw new ApplicationException("oops");
        }

        #region Properties

        /// <summary>
        /// Gets or sets the server.
        /// </summary>
        /// <value>The server.</value>
        public string Server { get; private set; }

        /// <summary>
        /// Gets or sets the port.
        /// </summary>
        /// <value>The port.</value>
        public int Port { get; private set; }

        /// <summary>
        /// Gets or sets the database.
        /// </summary>
        /// <value>The database.</value>
        public string Database { get; private set; }

        /// <summary>
        /// Gets or sets the username.
        /// </summary>
        /// <value>The username.</value>
        public string Username { get; private set; }

        /// <summary>
        /// Gets or sets the password.
        /// </summary>
        /// <value>The password.</value>
        public string Password { get; private set; }

        #endregion
    
        /// <summary>
        /// Opens a connection to the database.
        /// </summary>
        /// <returns></returns>
        public IDbConnection Open()
        {
            IDbConnection dbConnection = new MySqlConnection(connectionString);

            dbConnection.Open();

            return dbConnection;
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <param name="args">The args.</param>
        public void ExecuteNonQuery(string sql, IList<DbParameter> parameters, params object[] args)
        {
            ExecuteNonQuery(string.Format(sql, args), parameters);
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        public void ExecuteNonQuery(string sql, IList<DbParameter> parameters)
        {
            using (var dbConnection = Open())
            {
                using (var command = dbConnection.CreateCommand())
                {
                    command.CommandText = sql;

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (MySqlException ex)
                    {
                        throw new MapperExeception(string.Format(ErrorMessage, sql, ex.Message), ex);
                    }                    
                }
            }
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        public void ExecuteNonQuery(string sql)
        {
            ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, IList<DbParameter> parameters)
        {
            object result;

            using (var dbConnection = Open())
            {
                using (var command = dbConnection.CreateCommand())
                {
                    command.CommandText = sql;

                    if (parameters != null)
                    {
                        foreach(var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }

                    result = command.ExecuteScalar();
                }
            }

            return result;
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="args">The args.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, IList<DbParameter> parameters, params string[] args)
        {
            return ExecuteScalar(string.Format(sql, args), parameters);
        }


        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        public IList<T> ExecuteList<T>(string sql)
        {
            return ExecuteList<T>(sql, new object[0]);
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="args">The args.</param>
        /// <returns></returns>
        public IList<T> ExecuteList<T>(string sql, params object[] args)
        {
            return ExecuteList<T>(string.Format(sql, args), new List<DbParameter>());
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public IList<T> ExecuteList<T>(string sql, IList<DbParameter> parameters)
        {
            List<T> results = new List<T>();

            using (var dbConnection = Open())
            {
                using (var command = dbConnection.CreateCommand())
                {
                    command.CommandText = sql;

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }

                    try
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while(reader.Read())
                            {
                                // Bind results 
                                var result = DataReaderBinder.Bind<T>(reader);

                                results.Add(result);
                            }
                        }
                    }
                    catch (MySqlException ex)
                    {
                        throw new MapperExeception(string.Format(ErrorMessage, sql, ex.Message), ex);
                    }
                }
            }

            return results;
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        public T ExecuteOne<T>(string sql)
        {
            return ExecuteOne<T>(sql, (object[])null);
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="args">The args.</param>
        /// <returns></returns>
        public T ExecuteOne<T>(string sql, params object[] args)
        {
            return ExecuteOne<T>(string.Format(sql, args), new List<DbParameter>());
        }

        /// <summary>
        /// Executes the specified SQL against the <see cref="Connection"/>.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public T ExecuteOne<T>(string sql, IList<DbParameter> parameters)
        {
            var result = default(T);

            using (var dbConnection = Open())
            {
                using (var command = dbConnection.CreateCommand())
                {
                    command.CommandText = sql;

                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }

                    try
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                // Bind results 
                                result = DataReaderBinder.Bind<T>(reader);
                            }                 
                        }
                    }
                    catch (MySqlException ex)
                    {
                        throw new MapperExeception(string.Format(ErrorMessage, sql, ex.Message), ex);
                    }
                }
            }

            return result;
        }
    }
}